package cn.wolfcode.cms.web.controller;

import cn.wolfcode.cms.domain.Product;
import jxl.Cell;
import jxl.Sheet;
import jxl.Workbook;
import jxl.write.Label;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
import jxl.write.WriteException;

import java.io.File;
import java.io.IOException;
import java.math.BigDecimal;
import java.util.ArrayList;
import java.util.List;

public class ExcelController {
    //导出
    public void excleOut() {
        List<Product> list = new ArrayList<>();
        WritableWorkbook book = null;
        try {
            // 创建一个excle对象
            book = Workbook.createWorkbook(new File("d:/excleTest/product.xls"));
            // 通过excle对象创建一个选项卡对象
            WritableSheet sheet = book.createSheet("商品列表", 0);
            // 创建一个单元格对象 列 行 值
            for (int i = 0; i < list.size(); i++) {
                Product product = list.get(i);
                Label label1 = new Label(0, i, String.valueOf(product.getId()));
                Label label2 = new Label(1, i, product.getName());
                Label label3 = new Label(2, i, product.getSn());
                Label label4 = new Label(3, i, product.getCostPrice().toString());
                Label label5 = new Label(4, i, product.getSalePrice().toString());
                Label label6 = new Label(5, i, product.getIntegral());
                Label label7 = new Label(6, i, product.getSpecification());
                Label label8 = new Label(7, i, product.getImgPath());
                Label label9 = new Label(8, i, product.getFirstLevel());
                Label label10 = new Label(9, i, product.getSecondLevel());
                Label label11 = new Label(10, i, product.getRemark());

                // 将创建好的单元格对象放入选项卡中
                sheet.addCell(label1);
                sheet.addCell(label2);
                sheet.addCell(label3);
                sheet.addCell(label4);
                sheet.addCell(label5);
                sheet.addCell(label6);
                sheet.addCell(label7);
                sheet.addCell(label8);
                sheet.addCell(label9);
                sheet.addCell(label10);
                sheet.addCell(label11);
            }
            // 写入目标路径
            book.write();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                book.close();
            } catch (WriteException | IOException e) {
                e.printStackTrace();
            }
        }
    }

    //导入
    public List<Product> excleIn() {
        List<Product> list = new ArrayList<>();
        Workbook workbook = null;
        try {
            // 获取Ecle对象
            workbook = Workbook.getWorkbook(new File("d:/excleTest/product.xls"));
            // 获取选项卡对象 第0个选项卡
            Sheet sheet = workbook.getSheet(0);
            // 循环选项卡中的值
            for (int i = 0; i < sheet.getRows(); i++) {
                Product product = new Product();
                // 获取单元格对象
                Cell cell0 = sheet.getCell(0, i);
                // 取得单元格的值,并设置到对象中
                product.setId(Long.valueOf(cell0.getContents()));
                // 获取单元格对象，然后取得单元格的值,并设置到对象中
                product.setName(sheet.getCell(1, i).getContents());
                product.setSn(sheet.getCell(2, i).getContents());
                Cell cell1 = sheet.getCell(3, i);
                product.setCostPrice(new BigDecimal(cell1.getContents()));
                Cell cell2 = sheet.getCell(4, i);
                product.setSalePrice(new BigDecimal(cell2.getContents()));
                product.setIntegral(sheet.getCell(5, i).getContents());
                product.setSpecification(sheet.getCell(6, i).getContents());
                product.setImgPath(sheet.getCell(7, i).getContents());
                product.setFirstLevel(sheet.getCell(8, i).getContents());
                product.setSecondLevel(sheet.getCell(9, i).getContents());
                product.setRemark(sheet.getCell(10, i).getContents());
                list.add(product);
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            workbook.close();
        }
        return list;
    }

}
